Kexi/Tutorials/Importing SQLite database into Kexi: Difference between revisions

From KDE UserBase Wiki
m (Fix minor typo)
No edit summary
Tag: Reverted
Line 1: Line 1:
<languages />
Unban KEMONO_PANTSU now. She has gotten her 286 accounts on 114 platforms banned for no valid reason. Account list: https://controlc.com/afd584e3
<translate>


<!--T:1-->
She has also been listed on the lists below for no valid reason.
{{Note|This tutorial works with Kexi 2.x or newer. Author [[User:Jstaniek|Jarosław Staniek]] ([[User talk:Jstaniek|talk]]). }}
```
metasmoke - #kemono-pantsu-spam-wave: https://metasmoke.erwaysoftware.com/domains/tags/4291?page=6&what=posts
Wikipedia - 進行中の荒らし行為: https://ja.wikipedia.org/wiki/WP:VIP#KEMONO_PANTSU
```


<!--T:2-->
Number of accounts banned on each website
It has been written as for supporting this help request  on the [http://forum.kde.org/viewtopic.php?f=221&t=118997 Kexi forum]. Any comments are welcome in that thread.
```
 
Phorge: 32
<!--T:30-->
Wikimedia Foundation - Global lock: 24
Note: There is alternative approach: https://forum.kde.org/viewtopic.php?f=220&t=139243
Japanese Wikipedia: 13
 
Stack Exchange - Network-wide: 13
==Introduction== <!--T:3-->
Wikimedia Phabricator: 11
 
teratail: 11
<!--T:4-->
MediaWiki: 9
This tutorial shows how to take an input file ''oaklandweather.sdb'' which is a native standard SQLite3 file containing one table with data and create database project that can be used with Kexi.
Portal Unofficial Wiki: 7
 
Meta Stack Exchange: 6
<!--T:5-->
Meta-Wiki: 6
The input SQLite3 file is available [http://kexi-project.org/download/tests/oaklandweather-2013-12-31.sdb.xz here] (compressed to 600KB using xz, contains publicly available data).
Stack Overflow: 5
 
Gaia Online: 4
==Steps== <!--T:6-->
Japanese Wikivoyage: 4
 
Mathematics Stack Exchange: 4
<!--T:7-->
Wikipedia: 4
1. In [[Special:myLanguage/Konsole|Konsole]] terminal you can use ''sqlite3'' console app to show schema (''Sqliteman'' GUI app would not show the schema):
Japanese Wiktionary: 3
 
Medium: 3
<!--T:8-->
Physics Stack Exchange: 3
sqlite3 oaklandweather.sdb
Science Fiction & Fantasy Stack Exchange: 3
 
Tripadvisor: 3
<!--T:9-->
Wikitech: 3
2. Then you can type:
Wikitravel: 3
 
Wiktionary: 3
<!--T:10-->
Worldbuilding Stack Exchange: 3
.schema
Academia Stack Exchange: 2
 
Armor Games: 2
<!--T:11-->
Drupal Answers: 2
3. This shows you schema for one table ''archive'':
Italian Wikipedia: 2
 
Kongregate: 2
</translate>
Qiita: 2
CREATE TABLE archive (
Stack Exchange Chat: 2
    dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY,
Unix & Linux Stack Exchange: 2
    usUnits INTEGER NOT NULL,
Warframe Forums: 2
    interval INTEGER NOT NULL,
Wikibooks: 2
    barometer REAL,
Wikidata: 2
    pressure REAL,
Wikimedia Incubator: 2
    altimeter REAL,
Wikispecies: 2
    inTemp REAL,
Wikiversity: 2
    outTemp REAL,
Wikivoyage: 2
    inHumidity REAL,
教えて!goo: 2
    outHumidity REAL,
3D Printing Stack Exchange: 1
    windSpeed REAL,
Amateur Radio Stack Exchange: 1
    windDir REAL,
Amino: 1
    windGust REAL,
Android Enthusiasts Stack Exchange: 1
    windGustDir REAL,
Anime & Manga Stack Exchange: 1
    rainRate REAL,
Anime UK News Forums: 1
    rain REAL,
AnimeBase: 1
    dewpoint REAL,
Appropedia: 1
    windchill REAL,
Arabic Wikipedia: 1
    heatindex REAL,
Arqade - Stack Exchange: 1
    ET REAL,
Artificial Intelligence Stack Exchange: 1
    radiation REAL,
Ask Different - Stack Exchange: 1
    UV REAL,
Ask Ubuntu: 1
    extraTemp1 REAL,
Astronomy Stack Exchange: 1
    extraTemp2 REAL,
Aviation Stack Exchange: 1
    extraTemp3 REAL,
Bioinformatics Stack Exchange: 1
    soilTemp1 REAL,
Bitcoin Stack Exchange: 1
    soilTemp2 REAL,
Cebuano Wikipedia: 1
    soilTemp3 REAL,
Chess Stack Exchange: 1
    soilTemp4 REAL,
Code Golf Stack Exchange: 1
    leafTemp1 REAL,
Code Review Stack Exchange: 1
    leafTemp2 REAL,
Combine OverWiki: 1
    extraHumid1 REAL,
Cross Validated - Stack Exchange: 1
    extraHumid2 REAL,
Dutch Wikipedia: 1
    soilMoist1 REAL,
Egyptian Arabic Wikipedia: 1
    soilMoist2 REAL,
Electrical Engineering Stack Exchange: 1
    soilMoist3 REAL,
Enpedia: 1
    soilMoist4 REAL,
Fake Japanese Wikipedia - jaatd63804.rakkoserver.net: 1
    leafWet1 REAL,
Fanlore: 1
    leafWet2 REAL,
French Wikipedia: 1
    rxCheckPercent REAL,
GameFAQs: 1
    txBatteryStatus REAL,
GameSpot: 1
    consBatteryVoltage REAL,
German Wikipedia: 1
    hail REAL,
German Wikivoyage: 1
    hailRate REAL,
Giant Bomb: 1
    heatingTemp REAL,
Information Security Stack Exchange: 1
    heatingVoltage REAL,
Japanese Language Stack Exchange: 1
    supplyVoltage REAL,
Japanese Wikiquote: 1
    referenceVoltage REAL,
Japanese Wikitravel: 1
    windBatteryStatus REAL,
Korean Wikipedia: 1
    rainBatteryStatus REAL,
LocalWiki: 1
    outTempBatteryStatus REAL,
Magento Stack Exchange: 1
    inTempBatteryStatus REAL
Mathematica Stack Exchange: 1
);
Meta Stack Overflow: 1
<translate>
Minecraft Wiki - Fandom: 1
 
Music: Practice & Theory Stack Exchange: 1
<!--T:12-->
MyAnimeList: 1
4. This table needs to be manually re-created using Kexi to make it match the original table as much as possible. Unfortunately -- because this is a big table.  
OpenStreetMap: 1
 
OpenStreetMap Wiki: 1
<!--T:13-->
Photography Stack Exchange: 1
To do so, you can create a new blank database file project using the Kexi app. Give it a name such as ''oaklandweather'' so Kexi will pick a name ''oaklandweather.kexi''. Do not close the Kexi project.
Pokémon Wiki - Fandom: 1
 
Polish Wikipedia: 1
<!--T:14-->
Radiopaedia: 1
5. In the ''oaklandweather.kexi'' project, start designing a new table.
Reddit: 1
 
Salesforce Stack Exchange: 1
<!--T:15-->
Server Fault: 1
*Type a name for every column presented by the schema in the step 3.
Spanish Wikipedia: 1
*Use the Property Editor on the right hand to give each column in this table proper data type.
Super User: 1
**For INTEGER types, eg. ''dateTime INTEGER'' as seen on the schema, select Integer Number in the Data Type column of the Table Designer.
Swedish Wikipedia: 1
**For REAL types, eg. ''barometer REAL'' as seen on the schema, select Floating Point Number in the Data Type column of the Table Designer.
TV Tropes: 1
*In addition, set the ''dateTime'' column as the primary key for the table in the Property Editor.
Test Wikidata: 1
*Also, ''usUnits'' and ''interval'' columns have NOT NULL specifier assigned. To do the same in Kexi, set Required property to Yes for these columns in the Property Editor.
Test Wikipedia: 1
 
Test2 Wikipedia: 1
 
Wikimania: 1
<!--T:16-->
Wikimedia API Portal: 1
{{Note|A hint: on Linux you can copy column names a bit faster by double clicking on the name in the table schema above, double clicking on a subsequent empty cell in the Field Caption column of the Table designer, and then pressing the middle mouse button to paste the previously made selection.}}
Wikimedia Commons: 1
 
Wikimedia Foundation Governance Wiki: 1
<!--T:17-->
Wikimedia Outreach: 1
{{Note|It is possible to create the table in Kexi using text editor and a SQL command but it would be reserved for more advanced users. Given there is enough interest instructions can be published.}}
Wikinews: 1
 
Wikiquote: 1
<!--T:18-->
Wikisource: 1
6. Save the table's design with the ''Archive'' caption. Physical table's name will be set by Kexi as ''archive'' what matches the table name in the ''oaklandweather.sdb'' file.
Wikispore: 1
 
Wikitravel Shared: 1
<!--T:19-->
WordPress Development Stack Exchange: 1
Note that after saving the design you can again open it for designing. The only remark is that upon saving the altered table design you would loose any data in the table, if there is any, so it is better to make sure the table design is perfect before any data is inserted into the table.
```
 
<!--T:20-->
Make sure there are all the 52 columns have been inserted in the ''archive'' table design and that all are in correct order and have proper type. Any difference can make the resulting ''archive'' table inaccessible for Kexi.
 
<!--T:21-->
When you are done, close the Kexi app.
 
<!--T:22-->
The Kexi file without data is available [http://kexi-project.org/download/tests/oaklandweather-empty.kexi here] for reference.
 
<!--T:23-->
7. So far the ''oaklandweather.kexi'' file has no data in it. Now you may be ready to execute the final command to import the data. In the Konsole terminal enter to the directory where the ''oaklandweather.sdb'' and ''oaklandweather.kexi'' files exist or add proper paths to them. Then type (or paste) the command:
 
<!--T:24-->
sqlite3 oaklandweather.sdb .dump | egrep -e "^(BEGIN|INSERT|ANALYZE|COMMIT)" | sqlite3 oaklandweather.kexi
 
<!--T:25-->
This works as follows:
 
<!--T:26-->
*Reports the ''oaklandweather.kexi'' file to a so-called SQL dump (a text representation) using the sqlite3 console app.
*Removes from this dump any lines that do not begin with BEGIN, INSERT, ANALYZE or COMMIT. This is needed because we are only interested in data of the table, not in creating a new table.
*Directs the resulting output to a ''sqlite3'' console app again, which receives all the commands from the output and applies them to the ''oaklandweather.kexi'' database file. The last command applied is COMMIT.
 
 
<!--T:27-->
8. Done! You can open the ''oaklandweather.kexi'' database project file in Kexi and open the 'archive' table in it to see all the data.
 
 
<!--T:28-->
The resulting Kexi file is available [http://kexi-project.org/download/tests/oaklandweather.kexi.xz here] (compressed to 650KB using xz).
 
<!--T:29-->
[[Category:Office]]
[[Category:Tutorials]]
</translate>

Revision as of 09:42, 24 June 2023

Unban KEMONO_PANTSU now. She has gotten her 286 accounts on 114 platforms banned for no valid reason. Account list: https://controlc.com/afd584e3

She has also been listed on the lists below for no valid reason. ``` metasmoke - #kemono-pantsu-spam-wave: https://metasmoke.erwaysoftware.com/domains/tags/4291?page=6&what=posts Wikipedia - 進行中の荒らし行為: https://ja.wikipedia.org/wiki/WP:VIP#KEMONO_PANTSU ```

Number of accounts banned on each website ``` Phorge: 32 Wikimedia Foundation - Global lock: 24 Japanese Wikipedia: 13 Stack Exchange - Network-wide: 13 Wikimedia Phabricator: 11 teratail: 11 MediaWiki: 9 Portal Unofficial Wiki: 7 Meta Stack Exchange: 6 Meta-Wiki: 6 Stack Overflow: 5 Gaia Online: 4 Japanese Wikivoyage: 4 Mathematics Stack Exchange: 4 Wikipedia: 4 Japanese Wiktionary: 3 Medium: 3 Physics Stack Exchange: 3 Science Fiction & Fantasy Stack Exchange: 3 Tripadvisor: 3 Wikitech: 3 Wikitravel: 3 Wiktionary: 3 Worldbuilding Stack Exchange: 3 Academia Stack Exchange: 2 Armor Games: 2 Drupal Answers: 2 Italian Wikipedia: 2 Kongregate: 2 Qiita: 2 Stack Exchange Chat: 2 Unix & Linux Stack Exchange: 2 Warframe Forums: 2 Wikibooks: 2 Wikidata: 2 Wikimedia Incubator: 2 Wikispecies: 2 Wikiversity: 2 Wikivoyage: 2 教えて!goo: 2 3D Printing Stack Exchange: 1 Amateur Radio Stack Exchange: 1 Amino: 1 Android Enthusiasts Stack Exchange: 1 Anime & Manga Stack Exchange: 1 Anime UK News Forums: 1 AnimeBase: 1 Appropedia: 1 Arabic Wikipedia: 1 Arqade - Stack Exchange: 1 Artificial Intelligence Stack Exchange: 1 Ask Different - Stack Exchange: 1 Ask Ubuntu: 1 Astronomy Stack Exchange: 1 Aviation Stack Exchange: 1 Bioinformatics Stack Exchange: 1 Bitcoin Stack Exchange: 1 Cebuano Wikipedia: 1 Chess Stack Exchange: 1 Code Golf Stack Exchange: 1 Code Review Stack Exchange: 1 Combine OverWiki: 1 Cross Validated - Stack Exchange: 1 Dutch Wikipedia: 1 Egyptian Arabic Wikipedia: 1 Electrical Engineering Stack Exchange: 1 Enpedia: 1 Fake Japanese Wikipedia - jaatd63804.rakkoserver.net: 1 Fanlore: 1 French Wikipedia: 1 GameFAQs: 1 GameSpot: 1 German Wikipedia: 1 German Wikivoyage: 1 Giant Bomb: 1 Information Security Stack Exchange: 1 Japanese Language Stack Exchange: 1 Japanese Wikiquote: 1 Japanese Wikitravel: 1 Korean Wikipedia: 1 LocalWiki: 1 Magento Stack Exchange: 1 Mathematica Stack Exchange: 1 Meta Stack Overflow: 1 Minecraft Wiki - Fandom: 1 Music: Practice & Theory Stack Exchange: 1 MyAnimeList: 1 OpenStreetMap: 1 OpenStreetMap Wiki: 1 Photography Stack Exchange: 1 Pokémon Wiki - Fandom: 1 Polish Wikipedia: 1 Radiopaedia: 1 Reddit: 1 Salesforce Stack Exchange: 1 Server Fault: 1 Spanish Wikipedia: 1 Super User: 1 Swedish Wikipedia: 1 TV Tropes: 1 Test Wikidata: 1 Test Wikipedia: 1 Test2 Wikipedia: 1 Wikimania: 1 Wikimedia API Portal: 1 Wikimedia Commons: 1 Wikimedia Foundation Governance Wiki: 1 Wikimedia Outreach: 1 Wikinews: 1 Wikiquote: 1 Wikisource: 1 Wikispore: 1 Wikitravel Shared: 1 WordPress Development Stack Exchange: 1 ```

Retrieved from "https://userbase.kde.org/index.php?title=Kexi/Tutorials/Importing_SQLite_database_into_Kexi&oldid=477145"