cordova app: javascript sqlite many to many relationship

I have a Cordova project that uses SQLite to store locally some data of user. I want to create a many to many relationship, but I'm not sure how to do it.

I've read in this other question that you need to enable many to many relationships... but is that necessary in Cordova project? If so, how do I do it?

Just to be sure, how could I ask to DB, once done this if user in table U has product from table P? Is different somehow to queries in 1-1 relations?

As you can see I'm not used to working with DB, so please, asume I'm noob.

Xcode and Db Browser for SQLite - DB is write protected

My iOS app is connecting to a SQLite db using FMDB. Upon app initialisation the db is created/copied if required.

I can then open the copied database in DB Browser and manipulate data. If I then run the app (let it finish) whilst leaving DB Browser open upon trying to manipulate data again DB Browser says that the database is write protected. If I close the database within DB Browser and re-open it then I can edit data fine.

It just seems to be a problem when running the app with DB Browser open then prevents DB Browser from accessing the DB.

Does anyone know if there is a way round this?

SQLite referencing

I am currently pulling data from an XML via elementtree and then placing it into an SQLite database. The database consists of 3 tables, hosts, vulns and cves.

The hosts table holds a unique id, a IP address and netbios name. The vulns table holds a unique id, a pluginID, pluginName, severity and a descriptiong (yes this is nessus output). The cve table holds all the cves associated with the vulns identified for each host.

The problem I am having is how do I tie all of this data together. The cves have been put in a table of there own because each host has multiple vulnerabilities which in tern have multiple cves. I know you can reference other tables and I have attempted to, but to no avail (refs can bee seen in table creation below). If anyone could point me in the correct direction that would be great.

Current Code

def create_db():
    db = sqlite3.connect('database.sqlite')
    cursor = db.cursor()
    cursor.execute('''drop table if exists hosts''')
    cursor.execute('''drop table if exists vulns''')
    cursor.execute('''drop table if exists cves''')
    db.commit()
    cursor.execute('''CREATE TABLE hosts(id INTEGER PRIMARY KEY, operating_system TEXT, host_ip TEXT, netbios_name TEXT)
''')
    cursor.execute('''CREATE TABLE vulns(vuln_id INTEGER PRIMARY KEY, pluginID TEXT, pluginName TEXT, severity INTEGER, description TEXT)''')
    cursor.execute('''CREATE TABLE cves(cve_id INTEGER PRIMARY KEY references hosts(host_ip), pluginID TEXT, cve TEXT)''')
    db.commit()
    db.close()

Database Example Tables

How to do the proper SQLite query to a table with several foreign keys?

I have a table with two foreign keys pointing to the same table:

 create table person (
     id integer not null,
     living_city integer,
     birth_city integer, 
     foreign key (living_city) references city(id),
     foreign key (birth_city) references city(id)
  )

 create table city (
     id integer not null,
     name varchar
 )

I have added some data so the tables looks like:

 person 
    1   peter   1   2
    2   mary    1   1
    3   ed      2   3

 city
    1   london
    2   paris
    3   rome

The question looks easy but I can find how to do it… I have already searched all google!!

I am looking for a query like the following but getting the foreign keys names, not the numbers(id):

SELECT name, living_city, birth_city FROM person

The desired result is:

  > peter   london  paris
  > mary    london  london
  > ed      paris   rome

Thank you!

Batching inserts with RxJava

I need to load data (up to 22,000 records) from a JSon feed and store them on my android device (in SQLite).

Doing individual inserts is easy to implement but slow. Ideally I would like to batch up the objects to insert and pass a list of these objects to the db.

I can see how to do it imperatively but I would really like to do it with RxJava but not sure how.

Thanks

SQLite order query not working

I am trying to create a highscores page in android, I am trying order my numbers by the field called KEY_HOTNESS which is an iteger field, the code below shows the query that I have tried.

Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null,null, null, null,  KEY_HOTTNESS ,null);

Python Logic Issue

I am using elementtree to parse an XML file and placing the data into an sqlite database. I have come across a problem that I believe could be solved by some better logic, that I am most likely missing. I am getting a local variable 'netbios_name' referenced before assignment error, this is the case for the operating_system variable as well. I understand why I am getting it but I am not certain on how to resolve the issue.

Any help would be appreciated.

Example XML Data

<ReportHost name="192.168.26.11"><HostProperties>
<tag name="HOST_END">Sat Apr 25 11:36:08 2015</tag>
<tag name="LastUnauthenticatedResults">1223744168</tag>
<tag name="Credentialed_Scan">false</tag>
<tag name="policy-used">Advanced Scan</tag>
<tag name="patch-summary-total-cves">5</tag>
<tag name="cpe-0">cpe:/o:microsoft:windows_2003_server::sp2 -&gt; Microsoft Windows 2003 Server Service Pack 2</tag>
<tag name="system-type">general-purpose</tag>
<tag name="operating-system">Microsoft Windows Server 2003 Service Pack 2</tag>
<tag name="mac-address">00:1f:19:f5:14:34</tag>
<tag name="traceroute-hop-2">192.168.26.11</tag>
<tag name="traceroute-hop-1">10.100.1.249</tag>
<tag name="traceroute-hop-0">10.100.1.254</tag>
<tag name="host-ip">192.168.26.11</tag>
<tag name="netbios-name">PLUTOAPP01</tag>
<tag name="HOST_START">Sat Apr 25 10:20:43 2015</tag>
</HostProperties>

Example Problem Code

def get_details(nessus_file):
    db = sqlite3.connect('database.sqlite')
    cursor = db.cursor()
    try:
        tree = ET.parse(nessus_file)
        for reporthost in tree.findall('/Report/ReportHost'):
            host = reporthost.get('name')
            for tag in reporthost.findall('.//HostProperties/tag'):
                if tag.get('name') == 'netbios-name':
                    netbios_name = tag.text
                elif tag.get('name') == 'operating-system':
                    operating_system = tag.text
                else:
                    pass
                #The if statements above^ are causing my issues along with the execute statement below
                cursor.execute('INSERT INTO hosts(host, netbios_name, operating_system) VALUES(?,?,?)', (host, netbios_name, operating_system,))

            for item in reporthost.findall('ReportItem'):
                sev = item.get('severity')
                name = item.get('pluginName')
                description = item.findtext('description')
                pluginid = item.get('pluginID')

                cursor.execute('INSERT INTO vulns(pluginName, severity, description, pluginID) VALUES(?,?,?,?)', (name,sev,description,pluginid,))
                for cve in item.getiterator('cve'):
                    cursor.execute('INSERT INTO cves(cve) VALUES(?)', (cve.text,))
        db.commit()
        db.close()
    except Exception as e:
        print e
        exit()

create_db()
get_details('file.nessus')